-- common..p_Get_InventoryDetails '--  ALL  --','--  ALL  --','','','','--  ALL  --'
use common
go
IF OBJECT_ID('dbo.p_Get_InventoryDetails') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.p_Get_InventoryDetails
    IF OBJECT_ID('dbo.p_Get_InventoryDetails') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.p_Get_InventoryDetails >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.p_Get_InventoryDetails >>>'
END
go
create proc p_Get_InventoryDetails
(
	@ItemCode			varchar(200)	= null,	-- ItemCode
	@ItemName			varchar(200)	= null,	-- ItemName
	@ItemClass			varchar(50)		= null,	-- ItemClass
	@Location			varchar(200)	= null,	-- Location
	@LotNumber			varchar(200)	= null,	-- LotNumber
	@PurchasedFrom		varchar(200)	= null,	-- PurchasedFrom
	@Length           	decimal(5,3)	= null, -- Length
	@Width              decimal(10,3)	= null  -- Width
 	) as
begin

	create table #value(Token varchar(255))
	create table #ItemName (ItemName varchar(50))
	create table #ItemClass (ItemClass varchar(50))
	create table #ItemCode (ItemCode varchar(50))
	create table #LotNumber (LotNumber varchar(50))
	create table #Location (Location varchar(50))
	create table #PurchasedFrom (PurchasedFrom varchar(50))
	
	if ((rtrim(@ItemName) ='--  ALL  --') OR( rtrim(@ItemName) is null))
	begin
		insert into #ItemName
		select distinct ItemName from common..t_ItemMaster 
	end
	else
	begin
		exec common..p_ParseString @ItemName,","
		insert into #ItemName
		select distinct ItemName
		from #value a, common..t_ItemMaster b
		where a.Token=b.ItemName 
		delete from #value
	end
	
	if ((rtrim(@ItemClass) ='--  ALL  --') OR (rtrim(@ItemClass) is null) )
	begin
		insert into #ItemClass
		select distinct ItemClass from common..t_ItemMaster 
	end
	else
	begin
		exec common..p_ParseString @ItemClass,","
		insert into #ItemClass
		select distinct ItemClass
		from #value a, common..t_ItemMaster b
		where a.Token=b.ItemClass
		delete from #value
	end
	
	if ((rtrim(@ItemCode) ='--  ALL  --')OR( rtrim(@ItemCode) is null) )
	begin
		insert into #ItemCode
		select distinct ItemCode from common..t_ItemMaster 
	end
	else
	begin
		exec common..p_ParseString @ItemCode,","
		insert into #ItemCode
		select distinct ItemCode
		from #value a, common..t_ItemMaster b
		where a.Token=b.ItemCode 
		delete from #value
	end
	
	if ((rtrim(@Location) ='--  ALL  --') OR (rtrim(@Location) is null))
	begin
		insert into #Location
		select distinct Location from common..t_ItemMaster 
	end
	else
	begin
		exec common..p_ParseString @Location,","
		insert into #Location
		select distinct Location
		from #value a, common..t_ItemMaster b
		where a.Token=b.Location 
		delete from #value
	end
	
	
	if ((rtrim(@LotNumber) ='--  ALL  --') OR( rtrim(@LotNumber) is null))
	begin
		insert into #LotNumber
		select distinct LotNumber from common..t_ItemMaster 
	end
	else
	begin
		exec common..p_ParseString @LotNumber,","
		insert into #LotNumber
		select distinct LotNumber
		from #value a, common..t_ItemMaster b
		where a.Token=b.LotNumber
		delete from #value
	end
	
	if ((rtrim(@PurchasedFrom) ='--  ALL  --') OR( rtrim(@PurchasedFrom) is null) )
	begin
		insert into #PurchasedFrom
		select distinct PurchasedFrom from common..t_ItemMaster 
	end
	else
	begin
		exec common..p_ParseString @PurchasedFrom,","
		insert into #PurchasedFrom
		select distinct PurchasedFrom
		from #value a, common..t_ItemMaster b
		where a.Token=b.PurchasedFrom
		delete from #value
	end
	
	/*
	if (rtrim(@Length) is null))
		select @Length =0;
	
	if (( rtrim(@Width ) is null) )
		select @Width =0;
	*/
	
		select * from common..t_ItemMaster itm
		, #ItemName		itn
		, #ItemCode   	itc
		, #ItemClass    itclass
		, #Location		lc
		, #LotNumber	ln
		, #PurchasedFrom pf
		where itm.ItemName	= 	itn.ItemName
		and itm.ItemCode 	=	itc.ItemCode
		and itm.ItemClass 	=	itclass.ItemClass
		and itm.Location	= 	lc.Location
		and itm.LotNumber 	=	ln.LotNumber
		and itm.PurchasedFrom = pf.PurchasedFrom
	--	and itm.Length >= @Length
	--	and itm.Width >= @Width
		
		return 0
end
go
EXEC sp_procxmode 'dbo.p_Get_InventoryDetails','unchained'
go
IF OBJECT_ID('dbo.p_Get_InventoryDetails') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.p_Get_InventoryDetails >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.p_Get_InventoryDetails >>>'
go
GRANT EXECUTE ON dbo.p_Get_InventoryDetails TO public
go